/* Copyright 2010 Michael Wenders, michaelwenders <at> gmx <dot> de

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

#include "lcmd_includes.h"

#include "lcmd_mysql.h"
#include "lcmd_core.h"

LcmdMySQL::LcmdMySQL(Lcmd *lcmd, string server, string user, string pass, string db, int port)
{
	this->lcmd = lcmd;

	this->server = server;
	this->user = user;
	this->pass = pass;
	this->db = db;
	this->port = port;
}

/* Client Part */
lcm::SVEC1D LcmdMySQL::getClients()
{
	string command = "SELECT Clients.Name FROM Clients ORDER BY Clients.Name ASC";
	lcm::SVEC1D clients;
	lcm::SVEC2D relation;

	this->execSQL(command,relation);

	if(this->lastError != 0)
	{
		ostringstream os; os << "Error executing SQL command while getting client information";
		this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
	}

	//is only a one line vector
	for(unsigned int i = 0; i < relation.size(); i++)
		for(unsigned int j = 0; j < relation.at(i).size(); j++)
			clients.push_back(relation.at(i).at(j));

	return clients;
}

int LcmdMySQL::addClient(string clientname)
{
	if(doesClientExist(clientname))
	{
		cout << "Client '" << clientname << "' already exists" << endl;
		return lcm::NOT_OK;
	}
	else
	{
		ostringstream command;
		command << "INSERT INTO lcm.Clients \
				 (CID,Name,LastSeen) \
				 VALUES (NULL , '" << clientname <<  "', '');";

		cout << "Adding client '" << clientname << "'" << endl;

		this->execSQL(command.str());
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while adding client";
			this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}
	}
	return lcm::OK;
}

int LcmdMySQL::removeClient(string clientname)
{
	if(!doesClientExist(clientname))
	{
		cout << "Client '" << clientname << "' does not exist" << endl;
		return lcm::NOT_OK;
	}
	else if(isClientInAGroup(clientname))
	{
		cout << "Client '" << clientname << "' is still in a group! Will not remove client!" << endl;
		return lcm::NOT_OK;
	}
	else
	{
		cout << "Removing client '" << clientname << "'" << endl;
		string command = "DELETE FROM lcm.Clients WHERE Clients.Name = \"";
		command += clientname;
		command += "\"";

		this->execSQL(command);
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while removing client";
			this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}
	}
	return lcm::OK;
}

bool LcmdMySQL::doesClientExist(string clientname)
{
	string command = "SELECT Clients.Name FROM Clients WHERE Clients.Name = \"";
	command += clientname;
	command += "\" ORDER BY Clients.Name ASC";

	lcm::SVEC2D relation;
	this->execSQL(command,relation);
	if(this->lastError != 0)
	{
		ostringstream os; os << "Error executing SQL command while cecking if client exists";
		this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
	}

	if(relation.size() > 0)
		return true;
	else
		return false;
}

int LcmdMySQL::getClientID(string clientname)
{
	if(!doesClientExist(clientname))
	{
		return 0;
	}
	else
	{
		string command = "SELECT Clients.CID \
						  FROM Clients \
						  WHERE Clients.Name = \"";
		command += clientname;
		command += "\" ORDER BY Clients.CID";

		lcm::SVEC2D relation;
		this->execSQL(command,relation);
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while getting clients ID";
			this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}

		return lcm::stringToInt(relation.at(0).at(0));
	}
}

void LcmdMySQL::showClients()
{
	lcm::SVEC1D clients = this->getClients();

	cout << "<Client> | <Client's Groups> | <Client's Scripts>\n" << endl;
	for(unsigned int i = 0; i < clients.size(); i++)
	{
		lcm::SVEC1D groups = this->getClientsGroups(clients.at(i));
		lcm::SVEC1D scripts = this->getClientsScripts(clients.at(i));
		cout << clients.at(i) << " | " << flush;
		for(unsigned int j = 0; j < groups.size(); j++)
			cout << groups.at(j) << " " << flush;
		cout << "| " << flush;
		for(unsigned int j = 0; j < scripts.size(); j++)
			cout << scripts.at(j) << " " << flush;
		cout << endl;
	}
}

/* Group Part */
lcm::SVEC1D LcmdMySQL::getGroups()
{
	lcm::SVEC1D groups;

	string command = "SELECT Gruppen.Name FROM Gruppen ORDER BY Gruppen.Name ASC";

	lcm::SVEC2D relation;
	this->execSQL(command,relation);
	if(this->lastError != 0)
	{
		ostringstream os; os << "Error executing SQL command while getting groups";
		this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
	}

	//is only a one line vector
	for(unsigned int i = 0; i < relation.size(); i++)
		for(unsigned int j = 0; j < relation.at(i).size(); j++)
			groups.push_back(relation.at(i).at(j));

	return groups;
}

int LcmdMySQL::addGroup(string groupname, string description="")
{
	if(doesGroupExist(groupname))
	{
		cout << "Group '" << groupname << "' already exists" << endl;
		return lcm::NOT_OK;
	}
	else
	{
		cout << "Adding group '" << groupname << "'" << endl;

		string command = "INSERT INTO lcm.Gruppen \
						 (GID,Name,Description) \
						 VALUES (NULL , '";
		command += groupname;
		command += "', '";
		command += description;
		command += "');";

		this->execSQL(command);
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while adding groups";
				this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}
	}
	return lcm::OK;
}

int LcmdMySQL::removeGroup(string groupname)
{
	if(!doesGroupExist(groupname))
	{
		cout << "Group '" << groupname << "' does not exist" << endl;
		return lcm::NOT_OK;
	}
	else
	{
		cout << "Deleting group '" << groupname << "'" << endl;

		string command = "DELETE FROM lcm.Gruppen WHERE Gruppen.Name = \"";
		command += groupname;
		command += "\"";

		this->execSQL(command);
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while removing group";
			this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}
	}
	return lcm::OK;
}

bool LcmdMySQL::doesGroupExist(string groupname)
{
	string command = "SELECT Gruppen.Name FROM Gruppen WHERE Gruppen.Name = \"";
	command += groupname;
	command += "\" ORDER BY Gruppen.Name ASC";

	lcm::SVEC2D relation;
	this->execSQL(command,relation);
	if(this->lastError != 0)
	{
		ostringstream os; os << "Error executing SQL command while checking if group exists";
		this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
	}

	if(relation.size() > 0)
		return true;
	else
		return false;
}

int LcmdMySQL::getGroupID(string groupname)
{
	if(!doesGroupExist(groupname))
	{
		return 0;
	}
	else
	{
		string command = "SELECT Gruppen.GID \
						  FROM Gruppen \
						  WHERE Gruppen.Name = \"";
		command += groupname;
		command += "\" ORDER BY Gruppen.GID";

		lcm::SVEC2D relation;
		this->execSQL(command,relation);
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while getting groups ID";
			this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}

		return lcm::stringToInt(relation.at(0).at(0));
	}
}

void LcmdMySQL::showGroups()
{
	lcm::SVEC1D groups = this->getGroups();

	cout << "<Group> | <Groups' Clients> | <Groups' Scripts>\n" << endl;
	for(unsigned int i = 0; i < groups.size(); i++)
	{
		lcm::SVEC1D clients = this->getGroupsClients(groups.at(i));
		lcm::SVEC1D scripts = this->getGroupsScripts(groups.at(i));
		cout << groups.at(i) << " | " << flush;
		for(unsigned int j = 0; j < clients.size(); j++)
			cout << clients.at(j) << " " << flush;
		cout << "| " << flush;
		for(unsigned int j = 0; j < scripts.size(); j++)
			cout << scripts.at(j) << " " << flush;
		cout << endl;
	}
}

/* Script-Part */
lcm::SVEC1D LcmdMySQL::getScripts()
{
	lcm::SVEC1D scripts;

	string command = "SELECT Skripte.Name FROM Skripte ORDER BY Skripte.Name ASC";

	lcm::SVEC2D relation;
	this->execSQL(command,relation);
	if(this->lastError != 0)
	{
		ostringstream os; os << "Error executing SQL command while getting scripts";
		this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
	}

	//is only a one line vector
	for(unsigned int i = 0; i < relation.size(); i++)
		for(unsigned int j = 0; j < relation.at(i).size(); j++)
			scripts.push_back(relation.at(i).at(j));

	return scripts;
}

int LcmdMySQL::addScript(string scriptname, string filename, string description="")
{
	if(doesScriptExist(scriptname))
	{
		cout << "Script '" << scriptname << "' already exists" << endl;
		return lcm::NOT_OK;
	}
	else
	{
		cout << "Adding script '" << scriptname << "'" << endl;

		string command = "INSERT INTO lcm.Skripte \
						 (SID,Name,Path,MD5,Description) \
						 VALUES (NULL , '";
		command += scriptname;
		command += "','";
		command += filename;
		command += "', '";
		command += lcm::getMD5(filename);
		command += "','";
		command += description;
		command += "');";

		this->execSQL(command);
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while adding script";
			this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}
	}
	return lcm::OK;
}

int LcmdMySQL::removeScript(string scriptname)
{
	if(!doesScriptExist(scriptname))
	{
		cout << "Script '" << scriptname << "' does not exist" << endl;
		return lcm::NOT_OK;
	}
	else if (isScriptInAGroup(scriptname))
	{
		cout << "Script '" << scriptname << "' is still in a group" << endl;
		return lcm::NOT_OK;
	}
	else
	{
		cout << "Deleting script '" << scriptname << "'" << endl;

		string command = "DELETE FROM lcm.Skripte WHERE Skripte.Name = \"";
		command += scriptname;
		command += "\"";

		this->execSQL(command);
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while removing script";
			this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}
	}
	return lcm::OK;
}

bool LcmdMySQL::doesScriptExist(string scriptname)
{
	string command = "SELECT Skripte.Name FROM Skripte WHERE Skripte.Name = \"";
	command += scriptname;
	command += "\" ORDER BY Skripte.Name ASC";

	lcm::SVEC2D relation;
	this->execSQL(command,relation);
	if(this->lastError != 0)
	{
		ostringstream os; os << "Error executing SQL command while checking if script exists";
		this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
	}

	if(relation.size() > 0)
		return true;
	else
		return false;
}

int LcmdMySQL::getScriptID(string scriptname)
{
	if(!doesScriptExist(scriptname))
	{
		return 0;
	}
	else
	{
		string command = "SELECT Skripte.SID \
						  FROM Skripte \
						  WHERE Skripte.Name = \"";
		command += scriptname;
		command += "\" ORDER BY Skripte.SID";

		lcm::SVEC2D relation;
		this->execSQL(command,relation);
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while getting scripts ID";
			this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}

		return lcm::stringToInt(relation.at(0).at(0));
	}
}

void LcmdMySQL::showScripts()
{
	lcm::SVEC1D scripts = this->getScripts();

	cout << "<Script> | <Script's Groups> | <Scripts' Clients>\n" << endl;
	for(unsigned int i = 0; i < scripts.size(); i++)
	{
		//lcm::SVEC1D groups = this->getScripsGroups(scripts.at(i));
		//lcm::SVEC1D clients = this->getScriptsClients(scripts.at(i));
		cout << scripts.at(i) << " | " << flush;
		//for(unsigned int j = 0; j < groups.size(); j++)
		//	cout << groups.at(j) << " " << flush;
		cout << "| " << flush;
		//for(unsigned int j = 0; j < clients.size(); j++)
		//	cout << clients.at(j) << " " << flush;
		cout << endl;
	}
}

string LcmdMySQL::getScriptsPath(string scriptname)
{
	if(!doesScriptExist(scriptname))
	{
		return "";
	}
	else
	{
		string command = "SELECT Skripte.Path \
						  FROM Skripte \
						  WHERE Skripte.Name = \"";
		command += scriptname;
		command += "\" ORDER BY Skripte.SID";

		lcm::SVEC2D relation;
		this->execSQL(command,relation);
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while getting scripts path";
				this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}

		return relation.at(0).at(0);
	}
}

string LcmdMySQL::getScriptsMD5(string scriptname)
{
	if(!doesScriptExist(scriptname))
	{
		return "";
	}
	else
	{
		string command = "SELECT Skripte.MD5 \
						  FROM Skripte \
						  WHERE Skripte.Name = \"";
		command += scriptname;
		command += "\" ORDER BY Skripte.SID";

		lcm::SVEC2D relation;
		this->execSQL(command,relation);
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while getting scripts md5";
			this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}

		return relation.at(0).at(0);
	}
}

/* Group-Client-Assignment */
int LcmdMySQL::addClientToGroup(string clientname, string groupname)
{
	string clientid = "";
	string groupid = "";

	if(!doesGroupExist(groupname))
	{
		cout << "Group '" << groupname << "' does not exist!" << endl;
		return lcm::NOT_OK;
	}
	else if(!doesClientExist(clientname))
	{
		cout << "Client '" << clientname << "' does not exist!" << endl;
		return lcm::NOT_OK;
	}

	clientid = lcm::intToString(getClientID(clientname));
	groupid = lcm::intToString(getGroupID(groupname));

	if(isClientInGroup(clientname,groupname))
	{
		cout << "Client is already in this group" << endl;
		return lcm::NOT_OK;
	}
	else
	{
		cout << "Client added to group" << endl;
		string command = "INSERT INTO lcm.Gruppen_Clients( \
						`CID` , `GID` ) \
						VALUES ( '";
		command += clientid;
		command += "', '";
		command += groupid;
		command += "');";

		this->execSQL(command);
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while adding client to group";
			this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}
	}

	return lcm::OK;
}

int LcmdMySQL::removeClientFromGroup(string clientname, string groupname)
{
	string clientid = "";
	string groupid = "";

	if(!doesGroupExist(groupname))
	{
		cout << "Group '" << groupname << "' does not exist!" << endl;
		return lcm::NOT_OK;
	}
	else if(!doesClientExist(clientname))
	{
		cout << "Client '" << clientname << "' does not exist!" << endl;
		return lcm::NOT_OK;
	}

	clientid = lcm::intToString(getClientID(clientname));
	groupid = lcm::intToString(getGroupID(groupname));

	if(!isClientInGroup(clientname,groupname))
	{
		cout << "Client is not in this group" << endl;
		return lcm::NOT_OK;
	}
	else
	{
		cout << "Removing client from this group" << endl;

		string command = "DELETE FROM lcm.Gruppen_Clients \
						  WHERE Gruppen_Clients.CID = \"";
		command += clientid;
		command += "\" AND Gruppen_Clients.GID = \"";
		command += groupid;
		command += "\"";

		this->execSQL(command);
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while removing client from group";
			this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}
	}

	return lcm::OK;
}

lcm::SVEC1D LcmdMySQL::getClientsGroups(string clientname)
{
	lcm::SVEC1D groups;

	string command = "SELECT Gruppen.Name \
			 FROM Gruppen, Gruppen_Clients, Clients \
			 WHERE Gruppen.GID = Gruppen_Clients.GID AND \
			 Clients.CID = Gruppen_Clients.CID AND \
			 Clients.Name = \"";
	command += clientname;
	command += "\" ORDER BY Gruppen.Name ASC";

	lcm::SVEC2D relation;
	this->execSQL(command,relation);
	if(this->lastError != 0)
	{
		ostringstream os; os << "Error executing SQL command while getting clients groups";
		this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
	}

	for(unsigned int i = 0; i < relation.size(); i++)
		for(unsigned int j = 0; j < relation.at(i).size(); j++)
			groups.push_back(relation.at(i).at(j));

	return groups;
}

bool LcmdMySQL::isClientInGroup(string clientname, string groupname)
{
	string command = "SELECT Gruppen.Name , Clients.Name FROM Gruppen, Gruppen_Clients, Clients \
					WHERE Gruppen.GID = Gruppen_Clients.GID AND \
					Clients.CID = Gruppen_Clients.CID AND \
					Clients.Name = \"";
	command += clientname;
	command += "\" AND Gruppen.Name = \"";
	command += groupname;
	command += "\"";

	lcm::SVEC2D relation;
	this->execSQL(command,relation);
	if(this->lastError != 0)
	{
		ostringstream os; os << "Error executing SQL command while checking if client is in group";
		this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
	}

	if(relation.size() == 1)
		return true;
	else
		return false;
}

bool LcmdMySQL::isClientInAGroup(string clientname)
{

	if(this->getClientsGroups(clientname).size() > 0)
		return true;
	else
		return false;
}

bool LcmdMySQL::hasGroupClients(string groupname)
{
	string command = "SELECT Gruppen.Name, Clients.Name \
					FROM (Gruppen_Clients RIGHT OUTER JOIN Clients \
					ON Gruppen_Clients.CID = Clients.CID) \
					RIGHT OUTER JOIN Gruppen ON Gruppen_Clients.GID = Gruppen.GID \
					WHERE Gruppen.Name = \"";
	command += groupname;
	command += "\"";

	lcm::SVEC2D relation;
	this->execSQL(command,relation);
	if(this->lastError != 0)
	{
		ostringstream os; os << "Error executing SQL command while checking if group has clients";
		this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
	}

	/* special case */
	if(relation.size() == 1 &&
	  relation.at(0).at(1) == "")
		return false;

	if(relation.size() > 0)
		return true;
	else
		return false;
}

lcm::SVEC1D LcmdMySQL::getGroupsClients(string groupname)
{
	string command = "SELECT Clients.Name \
						FROM (Gruppen_Clients RIGHT OUTER JOIN Clients \
						ON Gruppen_Clients.CID = Clients.CID) \
	 	 	 	 	 	RIGHT OUTER JOIN Gruppen ON Gruppen_Clients.GID = Gruppen.GID WHERE Gruppen.Name = \"";
	command += groupname;
	command += "\"";

	lcm::SVEC2D relation;
	this->execSQL(command,relation);
	if(this->lastError != 0)
	{
		ostringstream os; os << "Error executing SQL command while getting groups clients";
		this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
	}

	//is only a one line vector
	lcm::SVEC1D groupsClients;
	for(unsigned int i = 0; i < relation.size(); i++)
		for(unsigned int j = 0; j < relation.at(i).size(); j++)
			groupsClients.push_back(relation.at(i).at(j));

	return groupsClients;
}

/* Group-Script-Assignment */
int LcmdMySQL::addScriptToGroup(string scriptname, string groupname)
{
	string scriptid = "";
	string groupid = "";

	if(!doesGroupExist(groupname))
	{
		cout << "Group '" << groupname << "' does not exist!" << endl;
		return lcm::NOT_OK;
	}
	else if(!doesScriptExist(scriptname))
	{
		cout << "Script '" << scriptname << "' does not exist!" << endl;
		return lcm::NOT_OK;
	}

	scriptid = lcm::intToString(getScriptID(scriptname));
	groupid = lcm::intToString(getGroupID(groupname));

	if(isScriptInGroup(scriptname,groupname))
	{
		cout << "Script is already in this group" << endl;
		return lcm::NOT_OK;
	}
	else
	{
		cout << "Script added to group" << endl;
		string command = "INSERT INTO lcm.Gruppen_Skripte( \
						`SID` , `GID` ) \
						VALUES ( '";
		command += scriptid;
		command += "', '";
		command += groupid;
		command += "');";

		this->execSQL(command);
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while adding script to group";
			this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}
	}

	return lcm::OK;
}

int LcmdMySQL::removeScriptFromGroup(string scriptname, string groupname)
{
	string scriptid = "";
	string groupid = "";

	if(!doesGroupExist(groupname))
	{
		cout << "Group '" << groupname << "' does not exist!" << endl;
		return lcm::NOT_OK;
	}
	else if(!doesScriptExist(scriptname))
	{
		cout << "Script '" << scriptname << "' does not exist!" << endl;
		return lcm::NOT_OK;
	}

	scriptid = lcm::intToString(getScriptID(scriptname));
	groupid = lcm::intToString(getGroupID(groupname));

	if(!isScriptInGroup(scriptname,groupname))
	{
		cout << "Script is not in this group" << endl;
		return lcm::NOT_OK;
	}
	else
	{
		cout << "Removing script from this group" << endl;

		string command = "DELETE FROM lcm.Gruppen_Skripte \
						  WHERE Gruppen_Skripte.SID = \"";
		command += scriptid;
		command += "\" AND Gruppen_Skripte.GID = \"";
		command += groupid;
		command += "\"";

		this->execSQL(command);
		if(this->lastError != 0)
		{
			ostringstream os; os << "Error executing SQL command while removing script from group";
			this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
		}
	}

	return lcm::OK;
}

bool LcmdMySQL::isScriptInGroup(string scriptname, string groupname)
{
	string command = "SELECT Gruppen.Name , Skripte.Name FROM Gruppen, Gruppen_Skripte, Skripte \
					WHERE Gruppen.GID = Gruppen_Skripte.GID AND \
					Skripte.SID = Gruppen_Skripte.SID AND \
					Skripte.Name = \"";
	command += scriptname;
	command += "\" AND Gruppen.Name = \"";
	command += groupname;
	command += "\"";

	lcm::SVEC2D relation;
	this->execSQL(command,relation);
	if(this->lastError != 0)
	{
		cout << this->lastError;
	}

	if(relation.size() == 1)
		return true;
	else
		return false;
}

bool LcmdMySQL::isScriptInAGroup(string scriptname)
{
	if(this->getScriptsGroups(scriptname).size() > 0)
		return true;
	else
		return false;
}

lcm::SVEC1D LcmdMySQL::getGroupsScripts(string groupname)
{
	lcm::SVEC1D scripts;

	string command = "SELECT Skripte.Name \
			 FROM Gruppen, Gruppen_Skripte, Skripte \
			 WHERE Gruppen.GID = Gruppen_Skripte.GID AND \
			 Skripte.SID = Gruppen_Skripte.SID AND \
			 Gruppen.Name = \"";
	command += groupname;
	command += "\" ORDER BY Skripte.Name ASC";

	lcm::SVEC2D relation;
	this->execSQL(command,relation);
	if(this->lastError != 0)
	{
		ostringstream os; os << "Error executing SQL command while getting groups scripts";
		this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
	}

	for(unsigned int i = 0; i < relation.size(); i++)
		for(unsigned int j = 0; j < relation.at(i).size(); j++)
			scripts.push_back(relation.at(i).at(j));

	return scripts;
}

lcm::SVEC1D LcmdMySQL::getClientsScripts(string clientname)
{
	lcm::SVEC1D clientsGroups = this->getClientsGroups(clientname);
	lcm::SVEC2D groupsScripts;
	lcm::SVEC1D clientsScripts;

	for(unsigned int i = 0; i < clientsGroups.size(); i++)
		groupsScripts.push_back(this->getGroupsScripts(clientsGroups.at(i)));

	for(unsigned int i = 0; i < groupsScripts.size(); i++)
		for(unsigned int j = 0; j < groupsScripts.at(i).size(); j++)
			clientsScripts.push_back(groupsScripts.at(i).at(j));

	return clientsScripts;
}

lcm::SVEC1D LcmdMySQL::getScriptsGroups(string scriptname)
{
	lcm::SVEC1D groups;

	string command = "SELECT Gruppen.Name \
			 FROM Gruppen, Gruppen_Skripte, Skripte \
			 WHERE Gruppen.GID = Gruppen_Skripte.GID AND \
			 Skripte.SID = Gruppen_Skripte.SID AND \
			 Skripte.Name = \"";
	command += scriptname;
	command += "\" ORDER BY Gruppen.Name ASC";

	lcm::SVEC2D relation;
	this->execSQL(command,relation);
	if(this->lastError != 0)
	{
		ostringstream os; os << "Error executing SQL command while getting scripts groups";
		this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
	}

	for(unsigned int i = 0; i < relation.size(); i++)
		for(unsigned int j = 0; j < relation.at(i).size(); j++)
			groups.push_back(relation.at(i).at(j));

	return groups;

}

/* Client-Script-Assignment (maybe Client-Script-Log fits better */
int LcmdMySQL::addScriptToClient(string clientname, string scriptname, string datetime, int retCode)
{
	string command = "INSERT INTO lcm.Clients_Skripte(CID , SID, ExecutedOn, retCode) VALUES ( '";
	command += lcm::intToString(this->getClientID(clientname));
	command += "', '";
	command += lcm::intToString(this->getScriptID(scriptname));
	command += "', '";
	command += datetime;
	command += "', '";
	command += lcm::intToString(retCode);
	command += "');";

	this->execSQL(command);
	if(this->lastError != 0)
	{
		cout << this->lastError << endl;
		ostringstream os; os << "Error executing SQL command while adding script to client";
		os << " Command was: " << command;
		this->lcmd->getLogger()->write(os.str(),lcm::log::LOGGER_ERROR);
	}

	return lcm::OK;
}

int LcmdMySQL::execSQL(string sqlCommand, lcm::SVEC2D &relation)
{
	MYSQL *conn = mysql_init(NULL);

	if(!conn)
	{
		cout << "in init error: " << mysql_errno(conn) << endl;
		this->lastError = lcm::error::MYSQL_INIT_ERROR;
		return NULL;
	}

	if (mysql_real_connect(conn,server.c_str(),user.c_str(),pass.c_str(),db.c_str(),port,NULL,0) == NULL)
	{
		cout << "in connect error: " << mysql_errno(conn) << endl;
		this->lastError = lcm::error::MYSQL_CONNECT_ERROR;
		return NULL;
	}

	if(mysql_query(conn, sqlCommand.c_str()) != 0)
	{
		cout << "in query error: " << mysql_error(conn) << endl;
		this->lastError = lcm::error::MYSQL_QUERY_ERROR;
		return NULL;
	}

	MYSQL_RES* result = mysql_store_result(conn);
	if(!result)
	{
		//ignore this, because inserting data has no results, this would lead to an error
		//there might be a better solution to this
		//this->lastError = lcm::MYSQL_RESULT_ERROR;
		//return NULL;
	}

	this->lastError = 0;

	relation = this->createSVEC2D(result);
	mysql_close(conn);

	return 0;
}

int LcmdMySQL::execSQL(string sqlCommand)
{
	//call execSQL(string,lcm::SVEC2D &) with dummy svec2d
	lcm::SVEC2D dummy;
	this->execSQL(sqlCommand,dummy);

	if(this->lastError != 0)
		return -1;
	else
		return 0;
}

lcm::SVEC2D LcmdMySQL::createSVEC2D(MYSQL_RES *result)
{
	lcm::SVEC2D relation;

	if(result)
	{
		int fields = mysql_num_fields(result);

		MYSQL_ROW row = NULL;
		lcm::SVEC1D tempRow;

		while ( ( row = mysql_fetch_row(result)) != NULL )
		{
			tempRow.clear();
			for(int i = 0; i < fields; i++)
			{
				if(row[i])
				{
					tempRow.push_back(row[i]);
				}
				else
					tempRow.push_back("");
				//push lcm::SVEC1D to lcm::SVEC2D
			}
			relation.push_back(tempRow);
		}
	}
	return relation;
}
